package stores;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.LinkedList;
import java.util.List;

import workspace.Workspace;
import beans.Terem;

public class TeremStoreImpl implements TeremStore {

	@Override
	public List<Terem> loadTeremLista(Workspace workspace) throws SQLException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		List<Terem> teremLista = new LinkedList<Terem>();
		Terem terem = null;

		try {
			ps = workspace
					.getPreparedStatement("SELECT ID, ROWVERSION, TEREMNEV, RENDELTETES, FELSZERELTSEG, RENDEZES, ALAPAR, FELELOS, MEGEROSITES FROM TEREM WHERE AKTIV = 1");
			rs = ps.executeQuery();
			while (rs.next()) {
				terem = buildTerem(rs);
				teremLista.add(terem);
			}
			return teremLista;

		} finally {
			if (ps != null) {
				ps.close();
			}
			if (rs != null) {
				rs.close();
			}
		}
	}

	@Override
	public Terem loadTeremByName(Workspace workspace, String teremNev)
			throws SQLException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		Terem terem = null;

		try {
			ps = workspace
					.getPreparedStatement("SELECT ID, ROWVERSION, TEREMNEV, RENDELTETES, FELSZERELTSEG, RENDEZES, ALAPAR, FELELOS, MEGEROSITES FROM FOGLALO WHERE AKTIV = 1 AND MEGNEVEZES = ?");
			int i = 1;
			ps.setString(i, teremNev);
			rs = ps.executeQuery();
			while (rs.next()) {
				terem = buildTerem(rs);
			}
			return terem;

		} finally {
			if (ps != null) {
				ps.close();
			}
			if (rs != null) {
				rs.close();
			}
		}
	}

	@Override
	public Terem loadTeremById(Workspace workspace, long id)
			throws SQLException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		Terem terem = null;

		try {
			ps = workspace
					.getPreparedStatement("SELECT ID, ROWVERSION, TEREMNEV, RENDELTETES, FELSZERELTSEG, RENDEZES, ALAPAR, FELELOS, MEGEROSITES FROM TEREM WHERE AKTIV = 1 AND ID = ?");
			int i = 1;
			ps.setLong(i++, id);
			rs = ps.executeQuery();
			while (rs.next()) {
				terem = buildTerem(rs);
			}
			return terem;

		} finally {
			if (ps != null) {
				ps.close();
			}
			if (rs != null) {
				rs.close();
			}
		}
	}

	@Override
	public Terem insertTerem(Workspace workspace, Terem ujTerem)
			throws SQLException {
		PreparedStatement ps = null;
		workspace.startSession();

		try {
			ps = workspace
					.getPreparedStatement("INSERT INTO TEREM(WRITER, TEREMNEV, RENDELTETES, FELSZERELTSEG, RENDEZES, ALAPAR, FELELOS, MEGEROSITES, AKTIV) VALUES (?, ?, ?, ?, ?, ?, ?, ?, 1)");
			int i = 1;
			ps.setLong(i++, workspace.getAktualisOperator().getId());
			ps.setString(i++, ujTerem.getTeremNev());
			ps.setString(i++, ujTerem.getRendeltetes());
			ps.setString(i++, ujTerem.getFelszereltseg());
			ps.setString(i++, ujTerem.getRendezes());
			ps.setLong(i++, ujTerem.getAlapAr());
			ps.setString(i++, ujTerem.getFelelos());
			ps.setBoolean(i++, ujTerem.isMegerosites());
			int sorValtNum = ps.executeUpdate();
			if (sorValtNum == 1) {
				workspace.commit();
				ujTerem.setId(getGeneratedKey(workspace));
				ujTerem.setRowVersion(1);
				return ujTerem;
			} else {
				return null;
			}

		} catch (SQLException e) {
			workspace.rollback();
			throw new SQLException(e);
		} finally {
			if (ps != null) {
				ps.close();
			}
		}
	}

	@Override
	public Terem updateTerem(Workspace workspace, Terem updatedTerem)
			throws SQLException {
		PreparedStatement ps = null;
		Terem terem = null;

		workspace.startSession();

		StringBuilder sql = new StringBuilder();
		sql.append("UPDATE TEREM SET WRITER = ?");
		if (updatedTerem.getTeremNev() != null) {
			sql.append(", TEREMNEV = ?");
		}
		if (updatedTerem.getRendeltetes() != null) {
			sql.append(", RENDELTETES = ?");
		}
		if (updatedTerem.getFelszereltseg() != null) {
			sql.append(", FELSZERELTSEG = ?");
		}
		if (updatedTerem.getRendezes() != null) {
			sql.append(", RENDEZES = ?");
		}
		if (updatedTerem.getAlapAr() != null) {
			sql.append(", ALAPAR = ?");
		}
		if (updatedTerem.getFelelos() != null) {
			sql.append(", FELELOS = ?");
		}
		if (updatedTerem.isMegerosites() != null) {
			sql.append(", MEGEROSITES = ?");
		}

		sql.append(" WHERE ID = ? AND ROWVERSION = ?");

		try {
			ps = workspace.getPreparedStatement(sql.toString());
			int i = 1;
			ps.setLong(i++, workspace.getAktualisOperator().getId());
			if (updatedTerem.getTeremNev() != null) {
				ps.setString(i++, updatedTerem.getTeremNev());
			}
			if (updatedTerem.getRendeltetes() != null) {
				ps.setString(i++, updatedTerem.getRendeltetes());
			}
			if (updatedTerem.getFelszereltseg() != null) {
				ps.setString(i++, updatedTerem.getFelszereltseg());
			}
			if (updatedTerem.getRendezes() != null) {
				ps.setString(i++, updatedTerem.getRendezes());
			}
			if (updatedTerem.getAlapAr() != null) {
				ps.setLong(i++, updatedTerem.getAlapAr());
			}
			if (updatedTerem.getFelelos() != null) {
				ps.setString(i++, updatedTerem.getFelelos());
			}
			if (updatedTerem.isMegerosites() != null) {
				ps.setBoolean(i++, updatedTerem.isMegerosites());
			}

			ps.setLong(i++, updatedTerem.getId());
			ps.setLong(i++, updatedTerem.getRowVersion());
			int valtSorNum = ps.executeUpdate();
			if (valtSorNum == 1) {
				terem = this.loadTeremById(workspace, updatedTerem.getId());
				workspace.commit();
			}
			return terem;

		} catch (SQLException e) {
			workspace.rollback();
			throw new SQLException(e);
		} finally {
			if (ps != null) {
				ps.close();
			}
		}
	}

	@Override
	public boolean deleteTerem(Workspace workspace, long id)
			throws SQLException {
		PreparedStatement ps = null;

		workspace.startSession();

		try {
			ps = workspace
					.getPreparedStatement("UPDATE TEREM SET WRITER = ?, AKTIV = 0 WHERE ID = ?");
			int i = 1;
			ps.setLong(i++, workspace.getAktualisOperator().getId());
			ps.setLong(i++, id);
			int valtSorNum = ps.executeUpdate();
			if (valtSorNum == 1) {
				workspace.commit();
				return true;
			} else {
				return false;
			}

		} catch (SQLException e) {
			workspace.rollback();
			throw new SQLException(e);
		} finally {
			if (ps != null) {
				ps.close();
			}
		}
	}

	@Override
	public boolean deleteTeremForTest(Workspace workspace, long id)
			throws SQLException {
		PreparedStatement ps = null;

		workspace.startSession();

		try {
			ps = workspace
					.getPreparedStatement("DELETE FROM TEREM WHERE ID = ?");
			int i = 1;
			ps.setLong(i++, id);
			int valtSorNum = ps.executeUpdate();
			if (valtSorNum == 1) {
				workspace.commit();
				return true;
			} else {
				return false;
			}

		} catch (SQLException e) {
			workspace.rollback();
			throw new SQLException(e);
		} finally {
			if (ps != null) {
				ps.close();
			}
		}
	}

	private Terem buildTerem(ResultSet rs) throws SQLException {
		int i = 1;
		Terem newTerem = new Terem();
		newTerem.setId(rs.getLong(i++));
		newTerem.setRowVersion(rs.getLong(i++));
		newTerem.setTeremNev(rs.getString(i++));
		newTerem.setRendeltetes(rs.getString(i++));
		newTerem.setFelszereltseg(rs.getString(i++));
		newTerem.setRendezes(rs.getString(i++));
		newTerem.setAlapAr(rs.getLong(i++));
		newTerem.setFelelos(rs.getString(i++));
		newTerem.setMegerosites(rs.getBoolean(i++));
		return newTerem;

	}

	private long getGeneratedKey(Workspace workspace) throws SQLException {
		ResultSet rs = null;
		PreparedStatement ps = null;
		try {
			ps = workspace
					.getPreparedStatement("SELECT IDENT_CURRENT('TEREM')");
			rs = ps.executeQuery();
			if (rs.next()) {
				return rs.getLong(1);
			} else
				return -1L;
		} finally {
			if (ps != null) {
				ps.close();
			}
			if (rs != null) {
				rs.close();
			}
		}
	}

}
